﻿/***********************************************************************************************
Título:Script esquema general de la base de datos para el departamento de Computación SSC
Creado por: Dennis Umañaa, Leonardo Víquez, Jose Luis Ochoa.
Última Modificación:Leonardo Víquez - 25/05/2011
***********************************************************************************************/


/***********************************************************************************************
consideraciones: al crear la base de datos debe eliminarse el esquema público por defecto
***********************************************************************************************/
-- DROP SCHEMA public;

/***********************************************************************************************
Lenguajes
***********************************************************************************************/
--create language plpgsql;

/***********************************************************************************************
Esquemas
***********************************************************************************************/
-- Schema: "General"
-- DROP SCHEMA General;

CREATE SCHEMA General
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA General TO postgres;
GRANT ALL ON SCHEMA General TO public;
COMMENT ON SCHEMA General IS 'Esquema píblico por defecto. Almecena las entidades generales de los sistemas para la Carrera de Ingeniería en Computación en la Sede San Carlos';

-- Schema: "Activos"
-- DROP SCHEMA Activos;

CREATE SCHEMA Activos
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA General TO postgres;
COMMENT ON SCHEMA General IS 'Almecena las entidades pertenecientes al Sistema de Control de Activos de la Carrera de Ingeniería en Computación en la Sede San Carlos';

-- Schema: "Utils"
-- DROP SCHEMA Utils;

CREATE SCHEMA utils
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA utils TO postgres;
COMMENT ON SCHEMA Utils IS 'Esquema de utilerías para generer procedimientos almacenados.';

-- Schema: "Auditoria"
-- DROP SCHEMA Auditoria;

CREATE SCHEMA Auditoria
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA Auditoria TO postgres;
COMMENT ON SCHEMA Auditoria IS 'Esquema para almacenar información para auditorias internas de la base de datos.';


--CreaciÃ³in del esquema para las actas...
CREATE SCHEMA actas
  AUTHORIZATION postgres;
GRANT ALL ON SCHEMA actas TO postgres;
COMMENT ON SCHEMA actas IS 'Esquema para almacenar información para actas de la base de datos.';
----

/********************************utilerias****************************************************/


/***********************************************************************************************
TÃtulo:	Script para el esquema de activos
Creado por:(c) 2008 Technology Lab,
Ãºltima ModificaciÃ³n:Leonardo VÃquez - 12/7/2010
Ãºltima ModificaciÃ³n:Jose Luis Ochoa M. - 19/7/2010
***********************************************************************************************/


/**
* (c) 2008 Technology Lab,
* Stored Procedure Generator 0.2
*
* Features:
*	will generate stored procedure wrappers around a given <schema>.<table> pair to perform: inserts, retrieves, updates opertions.
*
* License
* This is utility is designed for PostgreSQL 7x and later and comes with absolutely NO WARRANTY or Implied warranty.
* licensed under GPL V3.0 License (http://www.gnu.org/licenses/gpl.html)
* Change; share at will and give proper credit to contributors
*
* How to install this utility:
*	1. Create a schema called utils (if it doesn't already exist)
*	2. Run this file from either a command line (pgsql) or a GUI client
*
* How to run the utility:
*	For a given <schema>.<table> you should run in SQL editor (command line or GUI):
*	SELECT utils.generateStoredProcedures(<schema>,<table>) ; and the utility will create the appropriate functions in the <schema> you specified.
*
* Things to know:
*	1. Tables accessed with double quotes on their names are not yet supported
*
* TODO:
*	1. Provide Security to avoid SQL injection for varchar|Text|char(x) fields using regex
*	2. Refine getters i.e getter by unique; foreign keys (not just primary keys)
*	3. Provide support for delete operations
*
* Contributors:
*	Steve L. Nyemba, nyemba@gmail.com
*	Gregg Boyle, greggboyle@gmail.com
*/


CREATE OR REPLACE FUNCTION utils.getFieldNames(p_schemaName varchar, p_tableName varchar) returns REFCURSOR AS $$
DECLARE
	oFieldNames REFCURSOR;
BEGIN
	OPEN oFieldNames FOR
	SELECT column_name,data_type
	FROM 
		information_schema.columns
	WHERE
		table_schema=p_schemaName and table_name = p_tableName
		;
    
	return oFieldNames ;
END;
$$ LANGUAGE PLPGSQL ;

CREATE OR REPLACE FUNCTION utils.generateUpdateStoredProcedure(p_schemaName VARCHAR, p_tableName VARCHAR) RETURNS VOID AS $$
DECLARE
	oRecord		RECORD;
	oTableDef	REFCURSOR;

	oFunctionBody 	    VARCHAR;
	oSQLCommand	        VARCHAR;
	oFilter		        VARCHAR;
	oParameterList	    VARCHAR;
	oFieldNameList	    VARCHAR;
	oPrimaryKeyName     VARCHAR;
	
BEGIN
    oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;
	-- Creating an update stored procedure for a given <p_schemaName>.<p_tableName>
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION ' ||p_schemaName||'.upd_'||p_tableName ||'(' ;
	oSQLCommand 	:= 'UPDATE '||p_schemaName ||'.'||p_tableName || ' SET ';
	OPEN oTableDef FOR SELECT  utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;
    
	oParameterList 	:= '';
	oFieldNameList	:= '';

	LOOP
	    FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/**
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/
            
            
			IF oParameterList != '' THEN
				oParameterList := oParameterList || ', ' ;
						
			END IF ;
			
			IF oFieldNameList != '' THEN
				oFieldNameList := oFieldNameList || ',' ;

			END IF ;
			
			oParameterList := oParameterList || 'p_' || oRecord.column_name || ' ' || oRecord.data_type ;

		-- Parameter list  and field name lists:
		-- Not taking into account agn which is the primary key

			IF oRecord.column_name != oPrimaryKeyName THEN
				oFieldNameList := oFieldNameList|| oRecord.column_name ||' = '||'p_'|| oRecord.column_name ;

			ELSE
				-- Building the filter query WHERE agn = p_agn
				oFilter := ' WHERE '||oRecord.column_name||' = '||'p_'||oRecord.column_name ||';';
			END IF ;
		ELSE
			-- We have to place the closing parenthesis and the Exit the loop
			--oParameterList := oParameterList || ')' ;
		        oParameterList := oParameterList || ', idUser integer)' ||CHR(13);
			EXIT ;
		END IF ;
		

	END LOOP ;
	oSQLCommand := oSQLCommand || oFieldNameList ||oFilter ;
	oFunctionBody := oFunctionBody || oParameterList;
	oFunctionBody := oFunctionBody || ' RETURNS VOID AS  ' ||CHR(13)|| CHR(36) || CHR(36) ||CHR(13)||'  BEGIN '||CHR(13);

	oFunctionBody := oFunctionBody || oSQLCommand||CHR(13) ;
        oFunctionBody := oFunctionBody || 'UPDATE auditoria.auditoria SET idusuario = idUser WHERE (idusuario is null);'||CHR(13);
	oFunctionBody := oFunctionBody || ' END; ' ||CHR(13)|| CHR(36)||CHR(36) ||' LANGUAGE PLPGSQL ' ||CHR(13); 


	CLOSE oTableDef ;
	--INSERT INTO gquery(query) VALUES (oFunctionBody) ;
    EXECUTE ''||oFunctionBody ||'' ;
END;
$$ LANGUAGE PLPGSQL ;

CREATE OR REPLACE FUNCTION utils.generateInsertStoredProcedure(p_schemaName varchar,p_tableName varchar) returns void as  $$
DECLARE
	oRecord 			RECORD;
	oTableDef			REFCURSOR;

	oFunctionBody		VARCHAR;
	oSQLCommand			VARCHAR;
	oParameterList		VARCHAR;
	oFieldNameList		VARCHAR;
	oParameterValues	VARCHAR;
	oPrimaryKeyName     VARCHAR;
BEGIN
        oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;
	oFunctionBody := 'CREATE OR REPLACE FUNCTION ' || p_schemaName || '.ins_' || p_tableName || '(';
	oSQLCommand :='INSERT INTO ' || p_schemaName || '.' || p_tableName || '(';
	OPEN oTableDef FOR SELECT utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef; 

	--FETCH oTableDef INTO oRecord;
	oParameterList := '';
	oFieldNameList :='' ;
	oParameterValues   :='' ;
LOOP
		FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/** 
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/
			IF oParameterList != '' THEN
				oParameterList := oParameterList || ', ' ;
				oFieldNameList := oFieldNameList || ',' ;
				oParameterValues := oParameterValues || ',' ;
			END IF ;
			/**
			* Parameter list  and field name lists:
			* Not taking into account agn which is the primary key
            		*/
			IF oRecord.column_name != oPrimaryKeyName THEN
				oParameterValues := oParameterValues ||'p_'|| oRecord.column_name;
				oParameterList := oParameterList || 'p_' || oRecord.column_name || ' ' || oRecord.data_type ;
				oFieldNameList := oFieldNameList || oRecord.column_name ;
			END IF ;
		ELSE
			/**
            		* At thiselect general.get_nacionalidades()
select * from general.get_nacionalidades(1)
insert into general.nacionalidades (descripcion)values ('costarricense')




s point nothing has been found in oRecord, 
            		* We can now proceed to close the SQL Command with either parenthesis or semi colon
            		* We have to place the closing parenthesis and the Exit the loop
            		*/
			oParameterList := oParameterList || ', idUser integer)'||CHR(13);--, idUsuario int)' ;
			oFieldNameList := oFieldNameList || ')';--', idUsuario integer)' ;
			oParameterValues := oParameterValues ||');';
			EXIT ;
		END IF ;
	
	END LOOP;
	-- Let's build the SQL Command string, remembering that the oParameterList and oFieldNameList already contains the closing parenthesis
	oSQLCommand := oSQLCommand || oFieldNameList ;
	oSQLCommand := oSQLCommand || ' VALUES (' || oParameterValues ;

--	oSQLCommand := oSQLCommand || 'update auditoria set idUduario=p_idUsuario where idUsuario="";';

	
	oFunctionBody := oFunctionBody || oParameterList;	
	oFunctionBody := oFunctionBody || ' RETURNS VOID AS  ' || CHR(36) || CHR(36) ||CHR(13)||'  BEGIN '||CHR(13);

	oFunctionBody := oFunctionBody || oSQLCommand ||CHR(13);--aqui debe agregarse el update para auditoria
	oFunctionBody := oFunctionBody || 'UPDATE auditoria.auditoria SET idusuario = idUser WHERE (idusuario is null);'||CHR(13);
	oFunctionBody := oFunctionBody || ' END; ' ||CHR(13)||CHR(36)||CHR(36) ||' LANGUAGE PLPGSQL ' ||CHR(13); 

	CLOSE oTableDef ;
	-- INSERT INTO gQuery(query) VALUES (''||oFunctionBody||'') ;
	EXECUTE ''|| oFunctionBody || '';
END;
$$ LANGUAGE PLPGSQL ;

CREATE OR REPLACE FUNCTION utils.generateGetStoredProcedure(p_schemaName VARCHAR,p_tableName VARCHAR) RETURNS VOID AS $$
DECLARE
	oFunctionBody 	VARCHAR;
	oSQLQuery	VARCHAR;
BEGIN
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION '||p_schemaName||'.get_' ||p_tableName||'() RETURNS setof '|| p_schemaName ||'.'|| p_tableName ||CHR(13)||' AS '||CHR(36)||CHR(36) ||CHR(13);
	oFunctionBody	:= oFunctionBody || ' DECLARE result '|| p_schemaName ||'.'|| p_tableName || '%rowtype ; BEGIN ' ||CHR(13);
	oSQLQuery	:= ' for result in (select * from '|| p_schemaName ||'.'|| p_tableName || ')' ||CHR(13);
	oSQLQuery	:= oSQLQuery || ' loop return next result; end loop;'||CHR(13);
	oFunctionBody 	:= oFunctionBody || oSQLQuery || ' END; '||CHR(36) ||CHR(36)||' LANGUAGE PLPGSQL; '||CHR(13);
	EXECUTE oFunctionBody ;
END;
$$LANGUAGE  PLPGSQL;

CREATE OR REPLACE FUNCTION utils.generateGetByAgnStoredProcedure(p_schemaName VARCHAR,p_tableName VARCHAR) RETURNS VOID AS $$
DECLARE
	oFunctionBody 	    VARCHAR ;
	oSQLQuery	        VARCHAR;
	oPrimaryKeyName     VARCHAR;
BEGIN
    oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION '||p_schemaName||'.get_' ||p_tableName||'(p_Agn INTEGER) RETURNS setof ' ||p_schemaName||'.'||p_tableName||' AS '||CHR(36)||CHR(36)||CHR(13);
	oFunctionBody	:= oFunctionBody || ' DECLARE RESULT '||p_schemaName||'.'||p_tableName||'%rowtype;'|| 
					     'BEGIN ' ||CHR(13);
	oSQLQuery	    := ' for result in (select * from '||p_schemaName||'.'||p_tableName ||' WHERE '||oPrimaryKeyName||' = p_Agn )' ||CHR(13);
	oFunctionBody 	:= oFunctionBody || oSQLQuery || ' loop return next result; end loop; '||CHR(13);
	oFunctionBody	:= oFunctionBody ||' END; '||CHR(36) ||CHR(36)||CHR(13)||' LANGUAGE PLPGSQL; '||CHR(13);
	--INSERT INTO gquery(query) VALUES(oFunctionBody) ;

	EXECUTE oFunctionBody ;

END;
$$ LANGUAGE PLPGSQL ;


CREATE OR REPLACE FUNCTION utils.generateStoredProcedures(p_schemaName varchar,p_tableName varchar) returns void as $$
DECLARE 
	oFieldNames REFCURSOR ;
	oReferenceCount INTEGER;
BEGIN
	/*
	* In This area we insure that the p_schemaName and p_tableName exist
	* If the item does not exist then an exception is thrown
	*/
	 SELECT count(*) INTO oReferenceCount
	FROM information_schema.tables
	WHERE  table_schema = p_schemaName and table_name = p_tableName;
	if oReferenceCount != 1 THEN
		RAISE EXCEPTION 'Invalid schema name or table name entered ';
	ELSE 
		-- Loading field Names and assigning them to respective functions/stored procedures


--		OPEN oFieldNames FOR SELECT utils.getFieldNames(p_schemaName,p_tableName) ;
--		FETCH oFieldNames INTO oFieldNames ;
		-- Creating an insert stored procedure
		PERFORM utils.generateInsertStoredProcedure(p_schemaName,p_tableName) ;
		PERFORM utils.generateGetByAgnStoredProcedure(p_schemaName,p_tableName);
		PERFORM utils.generateUpdateStoredProcedure(p_schemaName,p_tableName);
		PERFORM utils.generateGetStoredProcedure(p_schemaName,p_tableName);
		PERFORM utils.generateEraseStoreProcedure(p_schemaName,p_tableName);
		--PERFORM utils.generateTrigerStoreProcedure(p_schemaName,p_tableName);
		PERFORM utils.generateSearchStoredProcedure(p_schemaName,p_tableName);
--		CLOSE oFieldNames ;
	END IF ;
END;


$$ LANGUAGE PLPGSQL ;

CREATE OR REPLACE FUNCTION utils.getPrimaryKeyName(p_schemaName varchar, p_tableName varchar) returns varchar as $$
DECLARE
       v_keyColumnName varchar  ;
       v_ConstraintName varchar ;
BEGIN
     SELECT constraint_name
     FROM information_schema.table_constraints
     WHERE table_schema = p_schemaName AND table_name = p_tableName AND constraint_type='PRIMARY KEY'
     INTO v_ConstraintName ;
     /**
     * At this point we have the constraint name and we will look for the column that maps to the primary key contraint name
     */
     IF NOT FOUND THEN
        RAISE EXCEPTION 'Missing Primary Key for selected schema/table '; --||p_schemaName||'.'||p_tableName) ;
     ELSE
          SELECT column_name
          FROM information_schema.constraint_column_usage
          WHERE table_schema = p_schemaName AND table_name = p_tableName AND constraint_name = v_ConstraintName
          INTO v_keyColumnName ;
     END IF ;
     
     RETURN v_keyColumnName ;
END ;
$$ LANGUAGE PLPGSQL ;


CREATE OR REPLACE FUNCTION utils.generateEraseStoreProcedure(p_schemaName varchar, p_tableName varchar) returns VOID as $$
  DECLARE        
	oFunctionBody		VARCHAR;
	oSQLCommand		VARCHAR;
	oParameterList		VARCHAR;
	oFieldNameList		VARCHAR;
	oParameterValues	VARCHAR;
	oPrimaryKeyName         VARCHAR;
BEGIN
	oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName, p_tableName);
	
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION '||p_schemaName||'.br_' ||p_tableName||'(p_'||oPrimaryKeyName||' int, iduser integer) RETURNS VOID '||' AS '||CHR(36)||CHR(36)||CHR(13) ;
		
	oFunctionBody	:= oFunctionBody || ' BEGIN DELETE from '|| p_schemaName ||'.'|| p_tableName ||' where '||oPrimaryKeyName ||' = p_'||oPrimaryKeyName||';  ' ||CHR(13);	
	oFunctionBody   := oFunctionBody || 'UPDATE auditoria.auditoria SET idusuario = idUser WHERE (idusuario is null); END;'||CHR(13);
	oFunctionBody 	:= oFunctionBody ||CHR(36) ||CHR(36)||' LANGUAGE PLPGSQL; ';
	EXECUTE oFunctionBody ;
	
END;
$$ LANGUAGE PLPGSQL ;


CREATE OR REPLACE FUNCTION utils.generateTrigerStoreProcedure(p_schemaName varchar, p_tableName varchar) returns VOID as $$
  DECLARE        
	oFunctionBody		VARCHAR;	
	tempo		VARCHAR;
	oReferenceCount 	INTEGER;
BEGIN	
	
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION '||p_schemaName||'.auditoria' ||p_tableName||'()  RETURNS TRIGGER AS '||CHR(36)||CHR(36) ;
		
	oFunctionBody	:= oFunctionBody || ' BEGIN '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'IF (TG_OP =' ||CHR(39)||'DELETE'||CHR(39)||') THEN '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'INSERT INTO auditoria.auditoria (esquema, tabla, serial, tipo) '||CHR(13);
	tempo:= utils.getPrimaryKeyName(p_schemaName,p_tableName);
	oFunctionBody	:= oFunctionBody || 'SELECT '||CHR(39)||p_schemaName||CHR(39)||','||CHR(39)||p_tableName||CHR(39)||', OLD.'||tempo||','||CHR(39)||'D'||CHR(39)||';'||CHR(13);
	oFunctionBody	:= oFunctionBody || 'RETURN OLD ;'||CHR(13);
	oFunctionBody	:= oFunctionBody || 'ELSIF (TG_OP =' ||CHR(39)||'UPDATE'||CHR(39)||') THEN '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'INSERT INTO auditoria.auditoria (esquema, tabla, serial, tipo) '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'SELECT '||CHR(39)||p_schemaName||CHR(39)||','||CHR(39)||p_tableName||CHR(39)||', NEW.'||tempo||', '||CHR(39)||'U'||CHR(39)||'; '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'RETURN NEW; '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'ELSIF (TG_OP =' ||CHR(39)||'INSERT'||CHR(39)||') THEN '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'INSERT INTO auditoria.auditoria (esquema, tabla, serial, tipo) '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'SELECT '||CHR(39)||p_schemaName||CHR(39)||','||CHR(39)||p_tableName||CHR(39)||', NEW.'||tempo||', '||CHR(39)||'I'||CHR(39)||'; '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'RETURN NEW; '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'END IF; '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'RETURN NULL; '||CHR(13);
	oFunctionBody	:= oFunctionBody || 'END; '||CHR(13);
	oFunctionBody	:= oFunctionBody || CHR(36)||CHR(36);
	oFunctionBody	:= oFunctionBody || ' LANGUAGE plpgsql; ' ||CHR(13);	
	EXECUTE oFunctionBody ;

	oFunctionBody	:='auditoria'||p_tableName;

	SELECT count(trigger_catalog) INTO oReferenceCount
	FROM information_schema.triggers
	where trigger_schema = p_schemaName and trigger_name = oFunctionBody;	
	IF oReferenceCount != 0 THEN
		tempo	:='DROP TRIGGER '||oFunctionBody|| ' ON '||p_schemaName||'.'||p_tableName;
		EXECUTE tempo;
	END IF;
	
	
	oFunctionBody	:= 'CREATE TRIGGER auditoria'||p_tableName||CHR(13);
	oFunctionBody	:= oFunctionBody || ' AFTER INSERT OR DELETE OR UPDATE ON '||p_schemaName||'.'||p_tableName||CHR(13);
	oFunctionBody	:= oFunctionBody || ' FOR EACH ROW EXECUTE PROCEDURE ' ||p_schemaName||'.auditoria'||p_tableName||'();'||CHR(13);
	EXECUTE oFunctionBody ;	
	
	
END;
$$ LANGUAGE PLPGSQL ;


CREATE OR REPLACE FUNCTION utils.generateSearchStoredProcedure(p_schemaName VARCHAR, p_tableName VARCHAR) RETURNS VOID AS $$
DECLARE
	oRecord		RECORD;
	oTableDef	REFCURSOR;

	oFunctionBody 	    VARCHAR;
	oSQLCommand	        VARCHAR;	
	oParameterList	    VARCHAR;
	oFieldNameList	    VARCHAR;
	oPrimaryKeyName     VARCHAR;
	temporal     VARCHAR;
	
BEGIN
        oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;
	-- Creating an update stored procedure for a given <p_schemaName>.<p_tableName>
	oFunctionBody 	:= 'CREATE OR REPLACE FUNCTION ' ||p_schemaName||'.get_'||p_tableName ||'(' ;
	oSQLCommand 	:= CHR(13)||' for result in 
				(select * from '||p_schemaName ||'.'||p_tableName || ' where ';
	OPEN oTableDef FOR SELECT  utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;
    
	oParameterList 	:= '';
	oFieldNameList	:= '';
	temporal	:= '';

	LOOP
	    FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/**
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/
            
            
			IF oParameterList != '' THEN
				oParameterList := oParameterList || ', ' ;
						
			END IF ;
			
			IF oFieldNameList != '' THEN
				oFieldNameList := oFieldNameList || ',' ;				

			END IF ;
			
			oParameterList := oParameterList || 'p_' || oRecord.column_name || ' ' || oRecord.data_type ;

			IF temporal != '' THEN
				temporal := temporal || ' AND '||CHR(13) ;
						
			END IF ;

				oFieldNameList := oFieldNameList|| oRecord.column_name ||' = '||'p_'|| oRecord.column_name ;
				temporal       := temporal || '((p_'||oRecord.column_name || ' is null ) or( p_'||oRecord.column_name||'=' ||oRecord.column_name ||'))'||CHR(13); 


		ELSE
			-- We have to place the closing parenthesis and the Exit the loop
		        oParameterList := oParameterList || ', idUser integer)' ||CHR(13);
			EXIT ;			
		END IF ;		

	END LOOP ;	
	oFunctionBody := oFunctionBody || oParameterList || 'RETURNS setof '||p_schemaName||'.'||p_tableName||CHR(13);
	oFunctionBody := oFunctionBody ||' AS '|| CHR(36)|| CHR(36)||CHR(13);
	oFunctionBody := oFunctionBody ||' DECLARE result '||p_schemaName||'.'||p_tableName||'%rowtype;
					  BEGIN' ||CHR(13);
	oFunctionBody := oFunctionBody || oSQLCommand ||'('||temporal||'))';
	oFunctionBody := oFunctionBody || ' loop return next result; end loop;
					     END;'||CHR(13) ||CHR(36) ||CHR(36)|| 'LANGUAGE plpgsql; '||CHR(13);
	oFunctionBody := oFunctionBody || 'ALTER FUNCTION '||p_schemaName||'.get_'||p_tableName ||'('||oParameterList|| 'OWNER TO postgres;';
	CLOSE oTableDef ;	
    EXECUTE ''||oFunctionBody ||'' ;
END;
$$ LANGUAGE PLPGSQL ;





CREATE OR REPLACE FUNCTION utils.generatetira(p_schemaName VARCHAR, p_tableName VARCHAR) RETURNS varchar AS $$
DECLARE
	oRecord		RECORD;
	oTableDef	REFCURSOR;

	oParameterList	    VARCHAR;
	oFieldNameList	    VARCHAR;
	oPrimaryKeyName     VARCHAR;
	temporal     VARCHAR;
	
BEGIN
        oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;

	OPEN oTableDef FOR SELECT  utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;
    
	oParameterList 	:= '';	
	oFieldNameList	:= '';
	temporal	:= CHR(36);

	LOOP
	    FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/**
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/
			temporal	:= CHR(36);
            
			IF oParameterList != '' THEN			
				oParameterList := oParameterList||','||CHR(36);
						
			END IF ;			

			IF oRecord.column_name != oPrimaryKeyName THEN
			temporal :=oParameterList || oRecord.column_name;
			oParameterList :=temporal;
			temporal='';						
			END IF;
			
		ELSE
			-- We have to place the closing parenthesis and the Exit the loop
		        oParameterList := oParameterList||','||CHR(36)||'idUser';
			EXIT ;			
		END IF ;		

	END LOOP ;
	oParameterList := CHR(36)||oParameterList;
	return 	oParameterList;
END;
$$ LANGUAGE PLPGSQL ;

CREATE OR REPLACE FUNCTION utils.generatetirados(p_schemaName VARCHAR, p_tableName VARCHAR) RETURNS varchar AS $$
DECLARE
	oRecord		RECORD;
	oTableDef	REFCURSOR;

	oParameterList	    VARCHAR;
	oFieldNameList	    VARCHAR;
	oPrimaryKeyName     VARCHAR;
	temporal     VARCHAR;
	
BEGIN
        oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;

	OPEN oTableDef FOR SELECT  utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;
    
	oParameterList 	:= '';	
	oFieldNameList	:= '';
	temporal	:= CHR(36);

	LOOP
	    FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
			/*					
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			*/
			temporal	:= CHR(36);
            
			IF oParameterList != '' THEN			
				oParameterList := oParameterList ||CHR(39)||','||CHR(39)||CHR(36);
						
			END IF ;			

			IF oRecord.column_name != oPrimaryKeyName THEN
			temporal :=oParameterList || oRecord.column_name;
			oParameterList :=temporal;
			temporal='';						
			END IF;
			
		ELSE
			-- We have to place the closing parenthesis and the Exit the loop
		        oParameterList := oParameterList||CHR(39) ||','||CHR(39)||CHR(36)||'idUser'||CHR(39);
			EXIT ;			
		END IF ;		

	END LOOP ;
	oParameterList := CHR(39)||CHR(36)||oParameterList;
	return 	oParameterList;
END;
$$ LANGUAGE PLPGSQL ;


/*
CREATE OR REPLACE FUNCTION utils.generatetira(p_schemaName VARCHAR, p_tableName VARCHAR) RETURNS varchar AS $$
DECLARE
	oRecord		RECORD;
	oTableDef	REFCURSOR;

	oParameterList	    VARCHAR;
	oFieldNameList	    VARCHAR;
	oPrimaryKeyName     VARCHAR;
	temporal     VARCHAR;
	
BEGIN
        oPrimaryKeyName := utils.getPrimaryKeyName(p_schemaName,p_tableName) ;

	OPEN oTableDef FOR SELECT  utils.getFieldNames(p_schemaName,p_tableName);
	FETCH oTableDef INTO oTableDef;
    
	oParameterList 	:= '';	
	oFieldNameList	:= '';
	temporal	:= CHR(36);

	LOOP
	    FETCH oTableDef INTO oRecord ;
		-- Function body at this point should include Parameter list
		IF FOUND THEN
					
			* If we have a record Then we assign parameter list and continue building the SQL Command
			*
		 	* Inspecting if we must insert commas. The principal is as follows :
		 	*  If the parameter list is not empty i.e != '' then we have at least one element that is inserted and because the status is set to FOUND then it means the previous elements have to be appended with comma before adding the new fields found
		 	*  consider that the Parameter list is the same as the field name list that would be in the INSERT statement
			
			temporal	:= CHR(36);
            
			IF oParameterList != '' THEN			
				oParameterList := oParameterList ||CHR(39)||','||CHR(39)||CHR(36);
						
			END IF ;			

			IF oRecord.column_name != oPrimaryKeyName THEN
			temporal :=oParameterList || oRecord.column_name;
			oParameterList :=temporal;
			temporal='';						
			END IF;
			
		ELSE
			-- We have to place the closing parenthesis and the Exit the loop
		        oParameterList := oParameterList||CHR(39) ||','||CHR(39)||CHR(36)||'idUser'||CHR(39);
			EXIT ;			
		END IF ;		

	END LOOP ;
	oParameterList := CHR(39)||CHR(36)||oParameterList;
	return 	oParameterList;
END;
$$ LANGUAGE PLPGSQL ;
*/



/*
select utils.generateSearchStoredProcedure('general','personas');
select * from general.search_personas(null, null, 'Jose', 'Chavarria', null, null, null, null, null, null);

select utils.createTriger('general','nacionalidades');
select utils.erase('general','nacionalidades'); 
select general.erase_nacionalidades(5);

select utils.erase('general','personas');

insert into general.nacionalidades values(5,'USA');
select *from general.nacionalidades

select utils.getPrimaryKeyName('general','personas');
select * from general.get_personas(1);
delete from general.nacionalidades where idnacionalidad=4;

select * from general.personas
select general.get_nacionalidades()
select * from general.get_nacionalidades(1)
insert into general.nacionalidades (descripcion)values ('costarricense')

select * from general.nacionalidades

insert into general.nacionalidades values(1,'Costarricense');
insert into general.nacionalidades values(2,'PanameÃ±o');
insert into general.nacionalidades values(3,'Chilena');
insert into general.nacionalidades values(4,'Nicaraguense');

insert into general.personas values(1,'1-0631-0928','Jose','Ochoa','Madrigal',1,'11/02/87','M','E');
insert into general.personas values(2,'1-0123-0928','Maria','Cespedes','Madrigal',3,'01/02/90','F','E');
insert into general.personas values(3,'1-0456-0928','Jose','Chavarria','Perez',3,'03/03/87','M','E');
insert into general.personas values(4,'1-0431-0928','Jasmin','Rodriguez','Orozco',2,'11/03/85','F','E');
insert into general.personas values(5,'1-0252-0928','Erika','Mendez','Sandoval',4,'11/05/84','F','E');
insert into general.personas values(6,'1-0236-0928','Eric','Vargas','Sanabria',1,'11/05/83','M','E');
insert into general.personas values(7,'1-0678-0928','David','Chavez','Corniza',1,'11/04/80','M','E');
insert into general.personas values(8,'1-0698-0928','Eduargo','Artavia','Arias',2,'11/03/89','M','E');
insert into general.personas values(9,'1-0125-0928','Martin','Pescador','Delgado',4,'11/06/82','M','E');
insert into general.personas values(10,'1-0983-0928','Allan','Brito','Delgado',2,'11/10/81','M','E');
insert into general.personas values(11,'1-0111-0928','Soila','Rosa','Espinoza',3,'10/09/82','F','E');
*/

/*
por hacer:
1)para todos los storeprocedure's agregar el atributo de idUsuario y realizar a modificaciÃ³n de este atributo en la tabla de auditoria exepto para los get
2)generar automaticamente procedimientos para borrar por id, trigger de auditoria, busquedad multi-atributos
*/



/************************************* general **********************************************/



/***********************************************************************************************
TÃtulo:	Script esquema general de la base de datos para el departamento de ComputaciÃ³n SSC
Creado por: Dennis UmaÃ±a, Leonardo VÃquez, Jose Luis Ochoa.
Ãšltima ModificaciÃ³n:Leonardo VÃquez - 13/7/2010
***********************************************************************************************/


-- Esquema por defecto "General"
SET search_path = General;

/***********************************************************************************************
Tablas
***********************************************************************************************/
--drop sequence seq_nacionalidades_id;
create sequence seq_nacionalidades_id;
--drop TABLE nacionalidades
create table nacionalidades
(
  idNacionalidad int default nextval('seq_nacionalidades_id') primary key,
  descripcion varchar(50) not null
);
Comment on table nacionalidades is 'Tabla para el registro nacionalidades para asociarlas a la tabla de personas';

--drop sequence seq_personas_id;
create sequence seq_personas_id;
--drop TABLE personas
CREATE TABLE personas
(
  idPersona int default nextval('seq_personas_id') PRIMARY KEY,
  cedula varchar(15) unique check (cedula similar to '[0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
  nombre varchar(50) NOT NULL,
  apellido1 varchar(50) NOT NULL,
  apellido2 varchar(50) NOT NULL,
  titulo varchar(50) NULL,
  nacionalidad int NULL,
  fechaNacimiento timestamp NULL,
  sexo char(1) DEFAULT NULL check (sexo in ('M','F')),
  tipo char(1) NOT NULL check (tipo in ('E','F','O')),
  foreign key (nacionalidad) references nacionalidades
); 
Comment on table personas is 'Tabla para el registro de datos de personas. Incluye funcionarios y estudiantes';
Comment on column personas.tipo is 'E=Estudiante, F=Funcionario, O=Otros';
Comment on column personas.Sexo is 'M=Masculino, F=Femenino';

--drop sequence seq_telefonos_id;
create sequence seq_telefonos_id;
--drop TABLE telefonos
CREATE TABLE telefonos
(
  idPersona int NOT NULL,
  telefono varchar(9) check (telefono similar to '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
  FOREIGN KEY (idPersona) REFERENCES personas (idPersona),
  primary key (idPersona,telefono)
); 
Comment on table telefonos is 'Tabla para el registro de telefonos asociados a los registros de personas';


--drop sequence seq_usuarios_id;
create sequence seq_usuarios_id;
--drop TABLE usuarios
CREATE TABLE usuarios
(  
  idUsuario int default nextval('seq_usuarios_id')  primary key,
  idPersona int not null,
  usuario varchar(20) unique not null,
  clave varchar(20) not null,
  tipo char(1) not null check (tipo in ('A','V')),
  estado char(1) not null check (estado in ('A','I')),
  FOREIGN KEY (idPersona) REFERENCES personas
); 
Comment on table usuarios is 'Tabla para el registro nacionalidades para asociarlas a la tabla de personas';
COMMENT ON COLUMN usuarios.tipo IS 'A=Administrador V=Visitante';
COMMENT ON COLUMN usuarios.estado IS 'A=Activo I=Inactivo';



CREATE TABLE usuariosA
(  
  idUsuario int default nextval('seq_usuarios_id')  primary key,
  idPersona int not null,
  usuario varchar(20) unique not null,
  clave varchar(20) not null,
  tipo char(1) not null check (tipo in ('A','V','S')),
  estado char(1) not null check (estado in ('A','I')),
  FOREIGN KEY (idPersona) REFERENCES personas
); 
Comment on table usuarios is 'Tabla para el registro nacionalidades para asociarlas a la tabla de personas';
COMMENT ON COLUMN usuarios.tipo IS 'A=Administrador V=Visitante o Profesor S=Secretaria de actas';
COMMENT ON COLUMN usuarios.estado IS 'A=Activo I=Inactivo';

--drop TABLE auditoria.auditoria
CREATE TABLE auditoria.auditoria
(
  idAuditoria SERIAL PRIMARY KEY NOT NULL,
  esquema varchar(100) NOT NULL,
  tabla varchar(100) NOT NULL,
  serial int not null,
  tipo char(1) DEFAULT 'I' check (tipo in ('D','U', 'I')),
  idUsuario int default NULL,    
  fecha timestamp default current_timestamp,
  direccion varchar(16) default inet_client_addr()
);
Comment on table auditoria.auditoria is 'Tabla para el registro de movimientos para procesos de auditoría';
Comment on column auditoria.auditoria.tipo is 'D=Delete U=Update I=Insert';


/***********************************************************************************************
usuario base
***********************************************************************************************/
insert into general.nacionalidades (descripcion) values ('Costa Rica');
insert into general.personas (cedula,nombre,apellido1,apellido2,titulo,nacionalidad,fechaNacimiento,sexo,tipo) values ('2-0562-0727','Leonardo','Víquez','Acuña','Ing.',1,'07-08-1981','M','F');
insert into general.usuarios(idPersona,usuario,clave,tipo,estado) values (currval('seq_personas_id'),'leoviquez','12345','A','A');
insert into general.usuariosA(idPersona,usuario,clave,tipo,estado) values (currval('seq_personas_id'),'leoviquez','12345','A','A');



/************************************* activos  **********************************************/


/***********************************************************************************************
TÃtulo:	Script para el esquema de activos
Creado por:Dennis UmaÃ±a, Leonardo VÃquez, Jose Luis Ochoa.
Ãºltima ModificaciÃ³n:Leonardo VÃquez - 13/7/2010
***********************************************************************************************/

/***********************************************************************************************
Tablas
***********************************************************************************************/

--drop sequence activos.seq_ubicacionActivo_id;
create sequence activos.seq_ubicacionActivo_id;
--drop table activos.ubicacionActivo
CREATE TABLE activos.ubicacionActivo
(
  idUbicacion int default nextval('activos.seq_ubicacionActivo_id') PRIMARY KEY NOT NULL,
  nombre varchar(100) NOT NULL
);
Comment on table activos.ubicacionActivo is 'Ubicación física general para los activos. Ejemplo: laboratorios oficinas, plantas físicas';


--drop sequence activos.seq_detalleUbicacion_id;
create sequence activos.seq_detalleUbicacion_id;
--drop table activos.detalleUbicacion
CREATE TABLE activos.detalleUbicacion
(
  idDetalleUbicacion int default nextval('activos.seq_detalleUbicacion_id')PRIMARY KEY NOT NULL, 
  idUbicacion int NULL,
  descripcion varchar(100) NOT NULL,
  FOREIGN KEY (idUbicacion) REFERENCES activos.ubicacionActivo
);
Comment on table activos.detalleUbicacion is 'Ubicación exacta de donde se encuentran los activos';


--drop sequence activos.seq_estadoActivo_id;
create sequence activos.seq_estadoActivo_id;
--drop table activos.estadoActivo
CREATE TABLE activos.estadoActivo
(
  idEstadoActivo int default nextval('activos.seq_estadoActivo_id') PRIMARY KEY NOT NULL,
  nombre varchar(100) NOT NULL,
  descripcion varchar(300) NULL
);
Comment on table activos.estadoActivo is 'Estados que puedan adoptar los activos';



--drop sequence activos.seq_familiaActivo_id;
create sequence activos.seq_familiaActivo_id;
--drop table activos.familiaActivo
CREATE TABLE activos.familiaActivo
(
  Familia int default nextval('activos.seq_familiaActivo_id') PRIMARY KEY NOT NULL,
  nombre varchar(50) NOT NULL
); 
Comment on table activos.familiaActivo is 'Describe las diferentes clasificaciones a los que pertenecen los activos';

--drop sequence activos.seq_grupoCaracteristicas_id;
create sequence activos.seq_grupoCaracteristicas_id;
--drop table activos.grupoCaracteristicas
CREATE TABLE activos.grupoCaracteristicas
(
  idGrupoCaracteristicas int default nextval('activos.seq_grupoCaracteristicas_id') PRIMARY KEY NOT NULL,
  nombre varchar(50) NOT NULL,
  descripcion varchar(200) NOT NULL
); 
comment on table activos.grupoCaracteristicas is 'Registra grupos definidos de características';

--drop sequence activos.seq_tipoActivo_id;
create sequence activos.seq_tipoActivo_id;
--drop table activos.tipoActivo
CREATE TABLE activos.tipoActivo
(
  idTipoActivo int default nextval('activos.seq_tipoActivo_id') PRIMARY KEY NOT NULL ,
  tipo varchar(50) Unique NOT NULL,
  idFamilia int NOT NULL, 
  idGrupoCaracteristicas int NULL, 
  FOREIGN KEY (idGrupoCaracteristicas) REFERENCES activos.grupoCaracteristicas,
  FOREIGN KEY (idFamilia) REFERENCES activos.familiaActivo
); 
Comment on table activos.tipoActivo is 'Describe el detale del tipo de activo asociado a una familia determinada';
COMMENT ON COLUMN activos.tipoActivo.tipo IS 'Ejemplo: PC: (Memoria RAM, Procesador), Monitor: (Marca,Modelo), Proyectores, Handhelds';


--drop sequence activos.seq_unidades_id;
create sequence activos.seq_unidades_id;
--drop table activos.unidades
CREATE TABLE activos.unidades
(
  idUnidad int default nextval('activos.seq_unidades_id') PRIMARY KEY NOT NULL,
  nombre varchar(30) NOT NULL,
  abreviatura varchar(5) NOT NULL,
  UNIQUE (abreviatura)
); 
Comment on table activos.unidades is 'Describe los tipos de unidades con que serán medidas algunas características de los activos';


--drop sequence activos.seq_caracteristicaActivo_id;
create sequence activos.seq_caracteristicaActivo_id;
--drop table activos.caracteristicaActivo
CREATE TABLE activos.caracteristicaActivo
(
  idCaracteristicaActivo int default nextval('activos.seq_caracteristicaActivo_id') PRIMARY KEY NOT NULL,
  caracteristica varchar(50) NOT NULL,  
  tipoDato char(1) NOT NULL CHECK (tipodato in ('N','A','L')),
  listaSeleccion int NOT NULL,
  ayuda varchar(150) NULL,
  idUnidad int default NULL,
  FOREIGN KEY (idUnidad) REFERENCES activos.unidades
); 
Comment on table activos.caracteristicaActivo is 'Describe lo las diferentes características que pueda tener un activo';
COMMENT ON COLUMN activos.caracteristicaActivo.tipodato IS 'Tipo de valor que adopta. N=Numérico, A=Alfanumérico, L=Lista';



--drop sequence activos.seq_caracteristicaActivo_tipoActivo_id;
create sequence activos.seq_caracteristicaActivo_tipoActivo_id;
--drop table activos.caracteristicaActivo_tipoActivo
CREATE TABLE activos.caracteristicaActivo_tipoActivo
(
  idcaracteristicaActivo_tipoActivo int default nextval('activos.seq_caracteristicaActivo_tipoActivo_id') primary key not null,
  idCaracteristicaActivo int NOT NULL,
  idTipoActivo int NOT NULL,
  orden smallint  NOT NULL,
  Unique (idCaracteristicaActivo,idTipoActivo),
  FOREIGN KEY (idCaracteristicaActivo) REFERENCES activos.caracteristicaActivo, 
  FOREIGN KEY (idTipoActivo) REFERENCES activos.tipoActivo
); 
comment on table activos.caracteristicaActivo_tipoActivo is 'Almacena las relaciones entre los tipos de activos y las características de los mismos';
COMMENT ON COLUMN activos.caracteristicaActivo_tipoActivo.orden IS 'Orden en que serán solicitadas las características de los activos';


--drop sequence activos.seq_proveedores_id;
create sequence activos.seq_proveedores_id;
--drop table activos.proveedores
CREATE TABLE activos.proveedores
(
  idProveedor int default nextval('activos.seq_proveedores_id') primary key not null,
  Nombre varchar(20) NOT NULL,
  Direccion varchar (100) null,
  telefono varchar(9) check (telefono similar to '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') null,
  fechaIngreso date NULL
);
comment on table activos.proveedores is 'Registra los proveedores de activos';
comment on column activos.proveedores.fechaIngreso is 'Fecha en que el proveedor brinda servicios a la institución';


--drop sequence activos.seq_activos_id;
create sequence activos.seq_activos_id;
--drop table activos.activos
CREATE TABLE activos.activos
(
  idActivo int default nextval('activos.seq_activos_id') primary key not null,
  numeroPlaca varchar(20) unique NOT NULL,
  fechaIngreso date NOT NULL,
  fechacompra date default NULL,
  observaciones varchar(400) default NULL,  
  fechaGarantia date default NULL,
  idProveedor int default NULL,
  idPersona int NOT NULL,
  idTipoActivo int NOT NULL,
  idEstadoActivo int NOT NULL,
  idDetalleUbicacion int NOT NULL,  
  FOREIGN KEY (idProveedor) REFERENCES activos.proveedores,
  FOREIGN KEY (idPersona) REFERENCES general.personas, 
  FOREIGN KEY (idTipoActivo) REFERENCES activos.tipoActivo,
  FOREIGN KEY (idEstadoActivo) REFERENCES activos.estadoActivo,
  FOREIGN KEY (idDetalleUbicacion) REFERENCES activos.detalleUbicacion
);
comment on table activos.activos is 'Registra los activos';


--drop sequence activos.seq_activo_CaracteristicaActivo_id;
create sequence activos.seq_activo_CaracteristicaActivo_id;
--drop table activos.activo_CaracteristicaActivo
CREATE TABLE activos.activo_CaracteristicaActivo
(
  idActivo_CaracteristicaActivo int default nextval('activos.seq_activo_CaracteristicaActivo_id') primary key not null,
  idActivo int NOT NULL,
  idCaracteristicaActivo int NOT NULL,
  valor varchar(200) NOT NULL,
  notas varchar(200) default NULL,  
  unique  (idActivo,idCaracteristicaActivo),
  FOREIGN KEY (idActivo) REFERENCES activos.activos,
  FOREIGN KEY (idCaracteristicaActivo) REFERENCES activos.caracteristicaActivo
); 
comment on table activos.activo_CaracteristicaActivo is 'Registra el valor real de la característica asociada al activo';
comment on column activos.activo_CaracteristicaActivo.valor is 'Registra el valor de la característica, puede ser de distintos tipos de datos definidos en la tabla de características de activos';


--drop sequence activos.seq_listaSeleccion_id;
create sequence activos.seq_listaSeleccion_id;
--drop table activos.activo_listaSeleccion
CREATE TABLE activos.listaSeleccion
(  
  idlistaSeleccion int default nextval('activos.seq_listaSeleccion_id') PRIMARY KEY NOT NULL,
  descripcion varchar(50) default NULL,
  orden smallint default NULL,
  idCaracteristicaActivo int NOT NULL,
  FOREIGN KEY (idCaracteristicaActivo) REFERENCES activos.caracteristicaActivo
);
comment on table activos.listaSeleccion is 'Registra listas predeterminadas de características de activos';
comment on column activos.listaSeleccion.orden is 'Orden de acomodo de las características';

--drop sequence activos.seq_caracteristicasActivo_GrupoCaracteristicas_id;
create sequence activos.seq_caracteristicasActivo_GrupoCaracteristicas_id;
--drop table activos.caracteristicasActivo_GrupoCaracteristicas
CREATE TABLE activos.caracteristicasActivo_GrupoCaracteristicas
(
  idCaracteristicasActivo_GrupoCaracteristicas int default nextval('activos.seq_caracteristicasActivo_GrupoCaracteristicas_id') primary key not null,
  idGrupoCaracteristicas int NOT NULL,
  idCaracteristicaActivo int NOT NULL,
  orden int NOT NULL,
  Unique (idGrupoCaracteristicas,idCaracteristicaActivo),
  FOREIGN KEY (idGrupoCaracteristicas) REFERENCES activos.grupoCaracteristicas,
  FOREIGN KEY (idCaracteristicaActivo) REFERENCES activos.caracteristicaActivo
); 
comment on table activos.caracteristicasActivo_GrupoCaracteristicas is 'Registra grupos preestablecidos de características';
comment on column activos.caracteristicasActivo_GrupoCaracteristicas.orden is 'Orden de acomodo de las características del grupo';


--drop sequence activos.seq_tipoMovimientoActivo_id;
create sequence activos.seq_tipoMovimientoActivo_id;
--drop table activos.tipoMovimientoActivo
CREATE TABLE activos.tipoMovimientoActivo
(
  idTipoMovimientoActivo int default nextval('activos.seq_tipoMovimientoActivo_id')PRIMARY KEY NOT NULL,
  movimiento varchar(100) NOT NULL,
  descripcion varchar(500) NOT NULL,
  subtipo char(1) NOT NULL,
  cambiaEstado char(1) NOT NULL,
  cambiaDueno char(1) NOT NULL,
  cambiaCaracteristicas char(1) NOT NULL,
  cambiaUbicacion char(1) NOT NULL,
  idEstadoActivo int NOT NULL,
  FOREIGN KEY (idEstadoActivo) REFERENCES activos.estadoActivo
); 
comment on table activos.tipoMovimientoActivo is 'Registra los tipos de movimientos de activos';
COMMENT ON COLUMN activos.tipoMovimientoActivo.idEstadoActivo IS 'Nuevo estado que adopta el activo';
COMMENT ON COLUMN activos.tipoMovimientoActivo.subtipo IS '1=movimiento 2=mantenimiento';
COMMENT ON COLUMN activos.tipoMovimientoActivo.cambiaEstado IS 'S=SI N=NO';
COMMENT ON COLUMN activos.tipoMovimientoActivo.cambiaDueno IS 'S=SI N=NO';
COMMENT ON COLUMN activos.tipoMovimientoActivo.cambiaCaracteristicas IS 'S=SI N=NO';
COMMENT ON COLUMN activos.tipoMovimientoActivo.cambiaUbicacion IS 'S=SI N=NO';


--drop sequence activos.seq_movimientoActivo_id;
create sequence activos.seq_movimientoActivo_id;
--drop table activos.movimientoActivo
CREATE TABLE activos.movimientoActivo
(
  idMovimiento int default nextval('activos.seq_movimientoActivo_id') PRIMARY KEY NOT NULL,
  fecha date default now(),
  comentario varchar(2000) default NULL,  
  estado char(1) NOT NULL,
  idTipoMovimiento int NOT NULL,
  idActivo int NOT NULL,
  DuenoAnterior int default NULL,
  DuenoActual int default NULL,
  EstadoActual int default NULL,
  EstadoAnterior int default NULL,
  UbicacionAnterior int default NULL,
  UbicacionActual int default NULL,  
  FOREIGN KEY (idTipoMovimiento) REFERENCES activos.tipoMovimientoActivo,
  FOREIGN KEY (idActivo) REFERENCES activos.activos,
  FOREIGN KEY (DuenoAnterior) REFERENCES general.personas,
  FOREIGN KEY (DuenoActual) REFERENCES general.personas,
  FOREIGN KEY (EstadoActual) REFERENCES activos.estadoActivo,
  FOREIGN KEY (EstadoAnterior) REFERENCES activos.estadoActivo,
  FOREIGN KEY (UbicacionAnterior) REFERENCES activos.ubicacionActivo,
  FOREIGN KEY (UbicacionActual) REFERENCES activos.ubicacionActivo
); 
comment on table activos.movimientoActivo is 'Registra los movimientos que sufren los activos';
COMMENT ON COLUMN activos.movimientoActivo.estado IS 'P:Pendiente, A:Aplicado';

--drop sequence activos.seq_movimientoActivo_CaracteristicaActivo_id;
create sequence activos.seq_movimientoActivo_CaracteristicaActivo_id;
--drop table activos.movimientoActivo_CaracteristicaActivo
CREATE TABLE activos.movimientoActivo_CaracteristicaActivo
(
  idMovimientoActivo_CaracteristicaActivo int default nextval('activos.seq_movimientoActivo_CaracteristicaActivo_id') primary key not null,
  idMovimientoActivo int NOT NULL,
  idCaracteristicaActivo int NOT NULL,
  valor_anterior varchar(20) default NULL,
  valor_actual varchar(20) default NULL,
  unique (idMovimientoActivo,idCaracteristicaActivo),
  FOREIGN KEY (idMovimientoActivo) REFERENCES activos.movimientoActivo,
  FOREIGN KEY (idCaracteristicaActivo) REFERENCES activos.caracteristicaActivo
); 
comment on table activos.movimientoActivo_CaracteristicaActivo is 'Registra los cambios de características por movimientos de activos';


--drop sequence activos.seq_tomaFisica_id;
create sequence activos.seq_tomaFisica_id;
--drop table activos.tomaFisica
CREATE TABLE activos.tomaFisica
(
  idTomaFisica int default nextval('activos.seq_tomaFisica_id') PRIMARY KEY NOT NULL,
  fecha_Inicial date NOT NULL,
  fecha_Finalizacion date NOT NULL,
  descripcion varchar(200) NOT NULL  
); 
comment on table activos.tomaFisica is 'Registra las tomas físicas de los activos';


--drop sequence activos.seq_tomaFisica_Activos_id;
create sequence activos.seq_tomaFisica_Activos_id;
--drop table activos.tomaFisica_Activos
CREATE TABLE activos.tomaFisica_Activos
(
  idtomaFisica_Activos int default nextval('activos.seq_tomaFisica_Activos_id') primary key not null,
  comentario varchar(200),  
  estado char(1),
  idPersona int NOT NULL,
  idActivo int NOT NULL,
  idTomaFisica int NOT NULL,
  FOREIGN KEY (idPersona) REFERENCES general.personas,
  FOREIGN KEY (idActivo) REFERENCES activos.activos, 
  FOREIGN KEY (idTomaFisica) REFERENCES activos.tomaFisica
); 
comment on table activos.tomaFisica_Activos is 'Registra las acciones de las tomas físicas según el usuario que realiza el proceso';
COMMENT ON COLUMN activos.tomaFisica_Activos.estado IS '0=Correcto 1=No present 2=Presente pero con modificaciones 3=Activo';


/***********************************************************************************************
generaciÃ³n automÃ¡tica de procedimientos de inserciÃ³n captura y modificaciÃ³n de datos
***********************************************************************************************/

/*
select utils.generatestoredprocedures('activos','ubicacionactivo');
select utils.generatestoredprocedures('activos','detalleubicacion');
select utils.generatestoredprocedures('activos','estadoactivo');
select utils.generatestoredprocedures('activos','familiaactivo');
select utils.generatestoredprocedures('activos','tipoactivo');
select utils.generatestoredprocedures('activos','unidades');
select utils.generatestoredprocedures('activos','caracteristicaactivo');
select utils.generatestoredprocedures('activos','caracteristicaactivo_tipoactivo');
select utils.generatestoredprocedures('activos','proveedores');
select utils.generatestoredprocedures('activos','activos');
select utils.generatestoredprocedures('activos','activo_caracteristicaactivo');
select utils.generatestoredprocedures('activos','listaseleccion');
select utils.generatestoredprocedures('activos','grupocaracteristicas');
select utils.generatestoredprocedures('activos','caracteristicasactivo_grupocaracteristicas');
select utils.generatestoredprocedures('activos','tipomovimientoactivo');
select utils.generatestoredprocedures('activos','movimientoactivo');
select utils.generatestoredprocedures('activos','movimientoactivo_caracteristicaactivo');
select utils.generatestoredprocedures('activos','tomafisica');
select utils.generatestoredprocedures('activos','tomafisica_activos');
*/

/***********************************************************************************************
Trigger's
***********************************************************************************************/





/************************************* personal **********************************************/


set search_path=General;

create sequence seqUsuarioHorarioId;
CREATE TABLE usuarioHorario
(
idUsuarioHorario int default nextval('seqUsuarioHorarioId') PRIMARY KEY,
idUsuario  int not null,
semestre char(1) check(semestre in('I','II')) not null,
anno int NOT NULL,
tipoPersonal char(1) check(tipoPersonal in ('O','M')) not null,

foreign key (idUsuario) references usuarios
); 
Comment on table usuarioHorario is 'Tabla para la relación entre los usuarios y el horario';
comment on column usuarioHorario.tipoPersonal is 'O=Operador, M=Mantenimiento';

create sequence seqHorarioId;
CREATE TABLE horario
(
idHorario int default nextval('seqHorarioId') PRIMARY KEY,
idUsuarioHorario int not null,
horaInicio time not null,
horaFin time not null,
dia varchar not null,
idUbicacion int not null,

foreign key (idUsuarioHorario) references usuarioHorario,
foreign key(idUbicacion) references activos.ubicacionactivo
); 
Comment on table horario is 'Tabla para el registro del horario de los operadores y personal de mantenimiento.';

create sequence seqPeticionesId;
Create table peticiones
(
idPeticiones int default nextval('seqPeticionesId')primary key,
estado char(1) check (estado in ('A','R','S')) not null,
idHorarioSolicitante int not null,
idHorarioSolicitado int not null,
foreign key (idHorarioSolicitante) references general.horario on delete cascade,
foreign key (idHorarioSolicitado) references general.horario on delete cascade
);
comment on table peticiones is 'Tabla para la información de las peticiones';
comment on column peticiones.estado is 'A=Aprobado, R=Rechazado, S=Solicitado';

create sequence seqUsuarioPeticionesId;
create table usuarioPeticiones
(usuarioPeticionesId int default nextval('seqUsuarioPeticionesId') primary key,
idPeticion int not null,
idUsuario int not null,
foreign key (idPeticion) references general.peticiones,
 foreign key (idUsuario) references general.usuarios
);
comment on table usuarioPeticiones is 'Tabla para la relación entre los usuarios y las peticiones';

create sequence seqMensajesId;
create table mensajes
(idMensaje int default nextval('seqMensajesId') primary key,
recibe int not null,
estado char(1) check (estado in ('L','N' ) ) not null ,
fecha timestamp,
mensaje varchar(500) not null,

foreign key(recibe) references general.usuarios
);
comment on table mensajes is 'Tabla para almacenar los mensajes enviados a los usuarios';
comment on column mensajes.recibe is 'Persona que recibe el mensaje';
comment on column mensajes.estado is'L=Leído, N=No leído';

create sequence seqUsuarioMensajesId;
create table usuarioMensaje
(
idUsuarioMensaje int default nextval('seqUsuarioMensajesId') primary key,
idMensaje int not null,
idUsuario int not null,

foreign key (idMensaje) references mensajes,
foreign key(idUsuario) references usuarios
);
comment on table usuarioMensaje is 'Tabla con la relación entre el usuario y los mensajes';

create sequence seqSolicitudPrestamoId;
create table solicitudPrestamo
(idSolicitud int default nextval('seqSolicitudPrestamoId') primary key,
estado char(1) check (estado in('A','R','S','P')) not null,
aprobado int,
tipo char(1) check(tipo in ('I','E')) not null,
fechaSolicitud timestamp not null,
fechaInicio date not null,
horaInicio time not null,
fechaFin date not null,
horaFin time not null,

foreign key (aprobado) references usuarios
);
comment on table solicitudPrestamo is 'Tabla que almacena las solicitudes sobre prestamos de equipo o infraestructura';
comment on column solicitudPrestamo.estado is 'A=Aprobado, R=Rechazado, S=Solicitado P=Parcialmente Aprobado';
comment on column solicitudPrestamo.tipo is 'I=Infraestructura, E=Equipo';

create sequence seqPersonaSolicitudId;
create table personaSolicitud
(
idPersonaSolicitud int default nextval('seqPersonaSolicitudId') primary key,
idPersona int not null,
idSolicitud int not null,

foreign key (idPersona) references personas,
foreign key(idSolicitud)references  solicitudPrestamo
);
comment on table personaSolicitud is 'Tabla para la relación entre personas y solicitudes de prestamo';

create sequence seqSolicitudEquipoId;
create table solicitudEquipo
(
idSolicitudEquipo int default nextval('seqSolicitudEquipoId') primary key,
idActivo int not null,
idSolicitud int not null,
estado char(1) check(estado in ('A','R','S')) not null,
foreign key (idActivo) references activos.activos,
foreign key(idSolicitud) references solicitudPrestamo
);
comment on table solicitudEquipo is 'Tabla de la relación entre un activo y una solicitud de prestamo';
comment on column solicitudEquipo.estado is'El estado de la solicitud A=Aprobado,R=Rechazado,S=Solicitado';



create sequence seqSolicitudSecciones;
create table solicitudSecciones
(
idSolicitudSecciones int default nextval ('seqSolicitudSecciones') primary key,
idDetalleUbicacion int not null,
idSolicitud int not null,
estado char(1) check(estado in ('A','R','S')) not null,
foreign key (idSolicitud) references solicitudPrestamo,
foreign key (idDetalleUbicacion) references activos.detalleubicacion
);
comment on table solicitudSecciones is 'Tabla de la relación entre una solicitud de prestamo y una infraestructura. Se utiliza ubicaciÃ³n detalle ya que es la información de una sección de un edificio.';
comment on column solicitudSecciones.estado is'El estado de la solicitud A=Aprobado,R=Rechazado,S=Solicitado';

create sequence seqInstitucionesId;
create table instituciones
(
idInstituciones int default nextval ('seqInstitucionesId') primary key,
nombre varchar(50) not null,
contacto varchar (100)
);
comment on table instituciones is 'Tabla para almacenar las instituciones que realizan una solicitud de algun activo';

create sequence seqTelefonosInstituciones;
create table telefonosInstituciones
(
idTelefonosInstituciones int default nextval('seqTelefonosInstituciones') primary key,
idInstituciones int not null,
telefono varchar(9) check (telefono similar to '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
foreign key (idinstituciones) references instituciones
);
comment on table telefonosInstituciones is 'Tabla que almacena los telefonos de las instituciones';

create sequence seqInstitucionesSolicitud;
create table institucionesSolicitud
(
idInstitucionesSolicitud int default nextval('seqInstitucionesSolicitud') primary key,
idInstitucion int not null,
idSolicitud int not null,

foreign key (idInstitucion) references instituciones,
foreign key(idSolicitud)references  solicitudPrestamo
);
comment on table institucionesSolicitud is 'Tabla para la relación entre instituciones y solicitudes de prestamo';





/**************************************** actas *************************************************/



/***********************************************************************************************
TÃtulo:	Script esquema actas de la base de datos para el departamento de ComputaciÃ³n SSC
Creado por: Carlos Cespedes, Christian Mejias, Diego Morera.
Ãšltima ModificaciÃ³n: Carlos Cespedes - 13/7/2010
***********************************************************************************************/


-- Esquema por defecto "actas"
SET search_path = actas;

/***********************************************************************************************
Tablas
***********************************************************************************************/

--drop sequence  seq_agenda_id;
create sequence seq_agenda_id;
--drop table agenda;
CREATE TABLE agenda
(
idAgenda int default nextval('seq_agenda_id') PRIMARY KEY,
numero int not null,
tipo_agenda char(1) not null check (tipo_agenda in('P','O')),
tipo_sesion char(1) not null check (tipo_sesion in('O','E')),
lugar varchar(100) not null,
fecha timestamp not null,
estado char(1) default 'A' check (estado in('A','I'))
);
Comment on column agenda.tipo_agenda is 'P=Provisional O=Oficial';
Comment on column agenda.tipo_sesion is 'O=Ordinaria E=Extraordinaria';
Comment on table agenda is 'Tabla para el registro de las agendas, cada una de estas asociada a su respectiva acta';

--drop sequence  seq_acta_id;
create sequence seq_acta_id;
--drop table acta;

CREATE TABLE acta
(
idActa int default nextval('seq_acta_id') PRIMARY KEY,
id_agenda int not null,
encabezado varchar(200),
fecha timestamp not null,
lugar varchar(50) NOT NULL,
descripcion varchar(500),
estado char(1) default 'A' check (estado in('A','I')),
foreign key (id_agenda) references agenda on delete cascade
);
Comment on table acta is 'Tabla para el registro de actas';

--drop sequence  seq_asistencia_id;
create sequence seq_asistencia_id;
--drop table asistencia;
CREATE TABLE asistencia
(
idAsistencia int default nextval('seq_asistencia_id') PRIMARY KEY,
id_acta int NOT NULL,
titulo varchar(2) NOT NULL check (titulo in ('PE','MI','RE','SA')),
id_persona int NOT NULL,
estado varchar(2) NOT NULL check (estado in ('PR','AI','AJ')),
foreign key (id_acta) references acta on delete cascade,
foreign key (id_persona) references general.personas on delete cascade
);
Comment on table asistencia is 'Tabla para el registro de asistentes dentro de las actas';
Comment on column asistencia.titulo is 'PE=Preside, MI=Miembro del Consejo, RE=Representante estudiantil, SA=Secretaria de Actas';
Comment on column asistencia.estado is 'PR=Presente, AI=Ausente Injustificado, AJ=Ausente Justificado';

--drop sequence seq_puntos;
create sequence seq_puntos;
--drop table puntos;
CREATE TABLE puntos
(
idPuntos int default nextval('seq_puntos') PRIMARY KEY,
id_agenda int NOT NULL,
descripcion varchar(10000) NOT NULL,
estado char(1) default 'A' check (estado in('A','I')),
foreign key (id_agenda) references agenda on delete cascade
);
Comment on table puntos is 'Tabla para el registro de los puntos a tratar en la agenda';

--drop sequence seq_articulo;
create sequence seq_articulo;
--drop table articulo;
CREATE TABLE articulo
(
idArticulo int default nextval('seq_articulo') PRIMARY KEY,
id_acta int NOT NULL,
titulo varchar(200) NOT NULL,
numero smallint NOT NULL,
cuerpo varchar(1000) NOT NULL,
tipo varchar(2) NOT NULL check (tipo in ('FN','FR','TR','AV')),
estado char(1) default 'A' check (estado in('A','I')),
foreign key (id_acta) references acta on delete cascade
);
Comment on table articulo is 'Tabla para el registro de los artículos del acta';
Comment on column articulo.tipo is 'FN=Fondo, FR=Foro, TR=Trámite, AV= Asuntos Varios';

--drop sequence seq_acuerdo;
create sequence seq_acuerdo;
--drop table acuerdo;
CREATE TABLE acuerdo
(
idAcuerdo int default nextval('seq_acuerdo') PRIMARY KEY,
id_articulo int NOT NULL,
cuerpo varchar(10000),
foreign key (id_articulo) references articulo on delete cascade
);
Comment on table acuerdo is 'Tabla para el registro de acuerdos de cada artículo';

--drop sequence seq_votacion;
create sequence seq_votacion;
--drop table votacion;
CREATE TABLE votacion
(
idVotacion int default nextval('seq_votacion') PRIMARY KEY,
id_acuerdo int NOT NULL,
favor smallint NOT NULL,
contra smallint NOT NULL,
abstiene smallint NOT NULL,
firmeza smallint NOT NULL check (firmeza in (0,1)),
no_participa smallint not null,
foreign key (id_acuerdo) references acuerdo on delete cascade
);
Comment on table votacion is 'Tabla para el registro de la votaciones en cada acuerdo que se vota';
Comment on column votacion.firmeza is '0=NO Presenta Firmeza, 1=Presenta Firmeza';

--drop sequence seq_comentario;
create sequence seq_comentario;
--drop table comentario;
CREATE TABLE comentario
(
idComentario int default nextval('seq_comentario') PRIMARY KEY,
id_articulo int NOT NULL,
cuerpo varchar(10000),
foreign key (id_articulo) references articulo on delete cascade
);
Comment on table comentario is 'Tabla para el registro de comentarios en cada artículo';





/******************************************** procedures y trigers ***********************************************/


/*
Esta parte crea los Store procedure para todas las tablas, el primer atributo corresponde al esquema y el segundo a la tabla de la cual se crearan los
storeprocedure
*/

select utils.generatestoredprocedures('auditoria','auditoria'); 
select utils.generatestoredprocedures('general','personas'); 
select utils.generatestoredprocedures('general','nacionalidades'); 
select utils.generatestoredprocedures('general','telefonos'); 
select utils.generatestoredprocedures('general','usuarios'); 
select utils.generatestoredprocedures('general','horario'); 
select utils.generatestoredprocedures('general','usuariohorario'); 
select utils.generatestoredprocedures('general','instituciones'); 
select utils.generatestoredprocedures('general','institucionessolicitud'); 
select utils.generatestoredprocedures('general','mensajes'); 
select utils.generatestoredprocedures('general','personasolicitud'); 
select utils.generatestoredprocedures('general','peticiones'); 
select utils.generatestoredprocedures('general','solicitudequipo'); 
select utils.generatestoredprocedures('general','solicitudprestamo'); 
select utils.generatestoredprocedures('general','solicitudsecciones'); 
select utils.generatestoredprocedures('general','telefonosinstituciones'); 
select utils.generatestoredprocedures('general','usuariomensaje'); 
select utils.generatestoredprocedures('general','usuariopeticiones'); 
select utils.generatestoredprocedures('general','telefonosinstituciones'); 
select utils.generatestoredprocedures('general','usuariosa');


select utils.generatestoredprocedures('activos','ubicacionactivo');
select utils.generatestoredprocedures('activos','detalleubicacion');
select utils.generatestoredprocedures('activos','estadoactivo');
select utils.generatestoredprocedures('activos','familiaactivo');
select utils.generatestoredprocedures('activos','tipoactivo');
select utils.generatestoredprocedures('activos','unidades');
select utils.generatestoredprocedures('activos','caracteristicaactivo');
select utils.generatestoredprocedures('activos','caracteristicaactivo_tipoactivo');
select utils.generatestoredprocedures('activos','proveedores');
select utils.generatestoredprocedures('activos','activos');
select utils.generatestoredprocedures('activos','activo_caracteristicaactivo');
select utils.generatestoredprocedures('activos','listaseleccion');
select utils.generatestoredprocedures('activos','grupocaracteristicas');
select utils.generatestoredprocedures('activos','caracteristicasactivo_grupocaracteristicas');
select utils.generatestoredprocedures('activos','tipomovimientoactivo');
select utils.generatestoredprocedures('activos','movimientoactivo');
select utils.generatestoredprocedures('activos','movimientoactivo_caracteristicaactivo');
select utils.generatestoredprocedures('activos','tomafisica');
select utils.generatestoredprocedures('activos','tomafisica_activos');


select utils.generatestoredprocedures('actas','acta');
select utils.generatestoredprocedures('actas','acuerdo');
select utils.generatestoredprocedures('actas','agenda');
select utils.generatestoredprocedures('actas','articulo');
select utils.generatestoredprocedures('actas','asistencia');
select utils.generatestoredprocedures('actas','comentario');
select utils.generatestoredprocedures('actas','puntos');
select utils.generatestoredprocedures('actas','votacion');


-- Ahora los triggers para cada todas las tablas 
--crear triggers ligados al esquema de activos
select utils.generateTrigerStoreProcedure('general','nacionalidades');
select utils.generateTrigerStoreProcedure('general','personas');
select utils.generateTrigerStoreProcedure('general','telefonos');
select utils.generateTrigerStoreProcedure('general','usuarios');
select utils.generateTrigerStoreProcedure('general','horario');
select utils.generateTrigerStoreProcedure('general','instituciones');
select utils.generateTrigerStoreProcedure('general','institucionessolicitud');
select utils.generateTrigerStoreProcedure('general','mensajes');
select utils.generateTrigerStoreProcedure('general','peticiones');
select utils.generateTrigerStoreProcedure('general','solicitudequipo');
select utils.generateTrigerStoreProcedure('general','solicitudprestamo');
select utils.generateTrigerStoreProcedure('general','solicitudsecciones');
select utils.generateTrigerStoreProcedure('general','telefonosinstituciones');
select utils.generateTrigerStoreProcedure('general','usuariohorario');
select utils.generateTrigerStoreProcedure('general','usuariomensaje');
select utils.generateTrigerStoreProcedure('general','usuariopeticiones');
select utils.generateTrigerStoreProcedure('general','personasolicitud');
select utils.generateTrigerStoreProcedure('general','usuariosa');

--crear triggers ligados al esquema de general
select utils.generateTrigerStoreProcedure('activos','activos');
select utils.generateTrigerStoreProcedure('activos','activo_caracteristicaactivo');
select utils.generateTrigerStoreProcedure('activos','caracteristicaactivo');
select utils.generateTrigerStoreProcedure('activos','caracteristicaactivo_tipoactivo');
select utils.generateTrigerStoreProcedure('activos','caracteristicasactivo_grupocaracteristicas');
select utils.generateTrigerStoreProcedure('activos','detalleubicacion');
select utils.generateTrigerStoreProcedure('activos','estadoactivo');
select utils.generateTrigerStoreProcedure('activos','familiaactivo');
select utils.generateTrigerStoreProcedure('activos','grupocaracteristicas');
select utils.generateTrigerStoreProcedure('activos','listaseleccion');
select utils.generateTrigerStoreProcedure('activos','movimientoactivo');
select utils.generateTrigerStoreProcedure('activos','movimientoactivo_caracteristicaactivo');
select utils.generateTrigerStoreProcedure('activos','proveedores');
select utils.generateTrigerStoreProcedure('activos','tipoactivo');
select utils.generateTrigerStoreProcedure('activos','tipomovimientoactivo');
select utils.generateTrigerStoreProcedure('activos','tomafisica');
select utils.generateTrigerStoreProcedure('activos','tomafisica_activos');
select utils.generateTrigerStoreProcedure('activos','ubicacionactivo');
select utils.generateTrigerStoreProcedure('activos','unidades');


select utils.generateTrigerStoreProcedure('actas','acta');
select utils.generateTrigerStoreProcedure('actas','acuerdo');
select utils.generateTrigerStoreProcedure('actas','agenda');
select utils.generateTrigerStoreProcedure('actas','articulo');
select utils.generateTrigerStoreProcedure('actas','asistencia');
select utils.generateTrigerStoreProcedure('actas','comentario');
select utils.generateTrigerStoreProcedure('actas','puntos');
select utils.generateTrigerStoreProcedure('actas','votacion');




/*****************PROCEDURES CREADOS MANUALMENTE*********************************/

CREATE OR REPLACE FUNCTION general.ins_telefonos(p_idpersona integer, p_telefono character varying, iduser integer)
  RETURNS void AS
'
  BEGIN 
INSERT INTO general.telefonos(idpersona,telefono) VALUES (p_idpersona,p_telefono);
UPDATE auditoria.auditoria SET idusuario = idUser WHERE (idusuario is null);
 END; 
'
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION general.ins_telefonos(character varying, integer) OWNER TO postgres;


CREATE OR REPLACE FUNCTION general.br_usuariosa(p_idusuario integer, iduser integer)
  RETURNS void AS 'BEGIN update  general.usuariosa set estado=''I'' where idusuario = p_idusuario;  
UPDATE auditoria.auditoria SET idusuario = idUser WHERE idusuario = idUser; END;'
  LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION general.br_usuarios(integer, integer) OWNER TO postgres;





CREATE OR REPLACE FUNCTION general.insertar_peticionIntercambio(miHorario integer,nuevoHorario integer,usuarioSolicitante integer,usuarioRecibe integer ,iduser integer)
  RETURNS void AS
$BODY$
declare
p_idpeticion integer;
p_idmensaje integer;

begin
--Inserta la peticion
perform general.ins_peticiones('S', miHorario, nuevoHorario, iduser);
--Averigua el id de la peticion recien insertada
p_idpeticion := nextval('general.sequsuariohorarioid')-1;
--Inserta en la tabla usuarios peticiones
perform general.ins_usuariopeticiones(p_idpeticion, usuarioSolicitante, iduser);
--Envia un mensaje al usuario que se le solicita el intercambio
perform general.ins_mensajes(usuarioRecibe,'N',current_date,'Se le ha solicitado un cambio de horario', iduser);
--Averigua el id del mensaje recien insertado
p_idmensaje := nextval('general.sequsuariomensajesid')-1;
--Inserta en la tabla usuario mensaje
perform general.ins_usuariomensaje(p_idmensaje, usuarioSolicitante, iduser);


end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;





------------Funcion para insertar el horario de un estudiante del personal del TEC (operador-mantenimiento)
create or replace function general.ins_Personal(p_idpersona integer, p_anno integer, p_semestre character, p_tipoPersonal character,p_arregloUbicacion integer[],p_arregloDias character[],p_arregloHorasIni varchar[],p_arregloHorasFin  varchar[],p_idUser integer)
returns void
as $func$
declare
p_idusuariohorario integer;
begin

--Inserta el usuario, el horario, el semestre, el anno y retorna el id del usuariohorario
p_idusuariohorario := general.insertar_usuariohorario(p_idpersona,p_semestre,p_anno,p_tipoPersonal,p_idUser);


--Inserta cada uno de los diferentes horarios del estudiante
for i in array_lower(p_arregloDias,1).. array_upper(p_arregloDias,1) loop
insert into general.horario(idusuariohorario,horainicio,horafin,dia,idubicacion) values (p_idusuariohorario, CAST (p_arregloHorasIni[i] as time) , CAST (p_arregloHorasFin[i] as time),p_arregloDias[i],p_arregloUbicacion[i]);
end loop;

end;
$func$ LANGUAGE plpgsql;


------------Funcion auxiliar para insertar el horario de un estudiante del personal del TEC (operador-mantenimiento)
create or replace function general.insertar_usuariohorario(p_idpersona integer, p_semestre character,p_anno integer,p_tipoPersonal character,p_idUser integer)
returns integer
as $func$
declare
variable integer;
id_usuario integer;

begin
select idusuario into id_usuario from general.usuarios where idpersona = p_idpersona;
perform general.ins_usuariohorario(id_usuario , p_semestre , p_anno, p_tipoPersonal, p_idUser);

variable := currval('general.sequsuariohorarioid');

return variable;
end;
$func$ LANGUAGE plpgsql;



------------Funcion para modificar el horario de un estudiante del personal del TEC (mantenimiento)
create or replace function general.upd_PersonalMantenimiento(p_idpersona integer, p_anno integer, p_semestre character, p_tipoPersonal character,p_arregloUbicacion integer[],p_arregloDias character[],p_arregloHorasIni varchar[],p_arregloHorasFin  varchar[],p_idUser integer)
returns void
as $func$
declare
p_idusuariohorario integer;
begin

select idusuariohorario into p_idusuariohorario from general.usuariohorario inner join general.usuarios on usuarios.idusuario = usuariohorario.idusuario where usuarios.idpersona = p_idpersona and semestre = p_semestre  and anno = p_anno;

delete from general.horario where horario.idusuariohorario = p_idusuariohorario;

--Modifica el horario del estudiante
for i in array_lower(p_arregloDias,1).. array_upper(p_arregloDias,1) loop
insert into general.horario(idusuariohorario,horainicio,horafin,dia,idubicacion) values (p_idusuariohorario, CAST (p_arregloHorasIni[i] as time) , CAST (p_arregloHorasFin[i] as time),p_arregloDias[i],p_arregloUbicacion[i]);
end loop;

end;
$func$ LANGUAGE plpgsql;



------------Segunda funcion para modificar el horario de un estudiante del personal del TEC (operador)
create or replace function general.upd_PersonalOperador(p_idpersona integer, p_anno integer, p_semestre character, p_tipoPersonal character,p_arregloUbicacion integer[],p_arregloDias character[],p_arregloHorasIni varchar[],p_arregloHorasFin  varchar[],p_arregloIdHorario  varchar[],p_idUser integer)
returns void
as $func$
declare
p_idusuariohorario integer;
begin

select idusuariohorario into p_idusuariohorario from general.usuariohorario inner join general.usuarios on usuarios.idusuario = usuariohorario.idusuario where usuarios.idpersona = p_idpersona and semestre = p_semestre  and anno = p_anno;

--Modifica el horario del estudiante
for i in array_lower(p_arregloIdHorario,1).. array_upper(p_arregloIdHorario,1) loop
if p_arregloHorasIni[i] is null then
delete from general.horario where horario.idhorario = CAST (p_arregloIdHorario[i] as integer);
else
if  CAST (p_arregloIdHorario[i] as integer) = 0 then
insert into general.horario(idusuariohorario,horainicio,horafin,dia,idubicacion) values (p_idusuariohorario, CAST (p_arregloHorasIni[i] as time) , CAST (p_arregloHorasFin[i] as time),p_arregloDias[i],p_arregloUbicacion[i]);
else
update general.horario set horainicio = CAST (p_arregloHorasIni[i] as time),horafin =  CAST (p_arregloHorasFin[i] as time),dia = p_arregloDias[i],idubicacion = p_arregloUbicacion[i] where idhorario = CAST (p_arregloIdHorario[i] as integer);
end if;
end if;
end loop;

end;
$func$ LANGUAGE plpgsql;


------------Funcion para borrar el horario de un estudiante del personal del TEC (operador/mantenimiento)
create or replace function general.br_Personal(p_idpersona integer,p_anno integer, p_semestre character)
returns void
as $func$
declare
p_idusuariohorario integer;
begin

select idusuariohorario into p_idusuariohorario from general.usuariohorario inner join general.usuarios on usuarios.idusuario = usuariohorario.idusuario where usuarios.idpersona = p_idpersona and semestre = p_semestre  and anno = p_anno;

--Borra el horario del estudiante
delete from general.horario where horario.idusuariohorario = p_idusuariohorario;
delete from general.usuariohorario where usuariohorario.idusuariohorario = p_idusuariohorario;

end;
$func$ LANGUAGE plpgsql;



------------Funcion para obtener los horarios de los estudiantes del personal del TEC (operador)
create or replace function general.get_choqueOperador(p_arregloIdHorario  varchar[])
returns integer
as $func$
declare
p_idhorario integer;
p_estado integer;
begin

for i in array_lower(p_arregloIdHorario,1).. array_upper(p_arregloIdHorario,1) loop
select idhorario into p_estado from general.get_horario(CAST (p_arregloIdHorario[i] as integer));
if p_estado != 0 then
p_idhorario := 1;
else
p_idhorario := 0;
end if;
end loop;

return p_idhorario;
end;
$func$ LANGUAGE plpgsql;







create or replace function general.insertar_SolicitudGeneral(p_estado character, p_aprobado integer, p_tipo character, p_fechasolicitud timestamp without time zone, p_fechainicio date, p_horainicio time without time zone, p_fechafin date, p_horafin time without time zone, iduser integer,tipoSolicitante integer,idSolicitante integer,activos integer[], activo integer)

returns void
as $func$
declare
p_idsolicitud integer;
begin

--Inserta la solicitud y retorna el id de la misma
p_idsolicitud := general.insertar_Solicitud(p_estado, p_aprobado , p_tipo, p_fechasolicitud, p_fechainicio, p_horainicio, p_fechafin, p_horafin);

--Tabla auditoria
insert into auditoria.auditoria(esquema,tabla,serial,idusuario) values ('general','solicitudprestamo',p_idsolicitud,iduser);


--Utilizando el id de la insercion anterior pregunta si es una institucion o una persona la que inserta
--0=Persona 1=Institucion
if tipoSolicitante=0 then
perform general.ins_personasolicitud(idSolicitante, p_idsolicitud, iduser);
else 
perform general.ins_institucionessolicitud(idSolicitante, p_idsolicitud, iduser);
end if;

--Si es una solicitud de Equipo se debe trabajar el arreglo
if p_tipo='E' then
for i in array_lower(activos,1).. array_upper(activos,1) loop
perform general.ins_solicitudequipo(activos[i], p_idsolicitud, 'S',iduser);
end loop;
else 
perform general.ins_solicitudsecciones(activo, p_idsolicitud,'S', iduser);
end if;

end;
$func$ LANGUAGE plpgsql;








create or replace function general.insertar_Solicitud(
p_estado character, p_aprobado integer, p_tipo character, p_fechasolicitud timestamp without time zone, p_fechainicio date, p_horainicio time without time zone, p_fechafin date, p_horafin time without time zone)
returns integer
as $func$
declare

variable integer;

begin

insert into general.solicitudprestamo (estado,aprobado,tipo,fechasolicitud,fechainicio,horainicio,fechafin,horafin)  values (p_estado, p_aprobado , p_tipo, p_fechasolicitud, p_fechainicio, p_horainicio, p_fechafin, p_horafin);

variable := currval('general.seqsolicitudprestamoid' 	);

return variable;
end;
$func$ LANGUAGE plpgsql;




--select * from general.insertar_Solicitud('S',null,'E','1-1-2011','1-1-2011','08:00','2-1-2011','08:00');





SET search_path = General;
CREATE or replace FUNCTION verificarEstadoSolicitud() RETURNS trigger 
AS $$
declare 
idsoli int;
totalSolicitudes int;
pendientes int;
aprobadas int;
BEGIN 
--Recupera el id de la solicitud que se acaba de modificar
idsoli:=new.idsolicitud;

--Pregunta si hay m?s equipo solicitado en la misma solicitud
pendientes:= (select count (estado) from general.solicitudequipo where estado='S' and idsolicitud=idsoli and idsolicitudequipo<>new.idsolicitudequipo);
--RAISE EXCEPTION '%',pendientes;
--Si no hay m?s equipo solicitado
if pendientes=0 then

--Pregunta el total de equipo hecho en la solicitud
totalSolicitudes := (select count (idsolicitudequipo) from general.solicitudequipo where idsolicitud=idsoli);

--Pregunta la cantidad de equipo aprobadas en una misam solicitud
aprobadas:=(select count (estado) from general.solicitudequipo where idsolicitud=idsoli and estado='A');
if new.estado='A' then
	aprobadas:=aprobadas+1;
end if;


--Si las solicitudes de equipo son la misma cantidad de aprobadas
--RAISE EXCEPTION '% No hay cupo','';
--RAISE EXCEPTION '%',totalSolicitudes||'-'||aprobadas; 


	if totalSolicitudes=aprobadas then
	--La solicitud se aprueba
		update general.solicitudprestamo set estado='A'  where idsolicitud=idsoli;
--Si las solicitudes aprobadas son cero, se niega la solicitud
	elsif aprobadas=0 then
		update general.solicitudprestamo set estado='R'  where idsolicitud=idsoli;
--Si hay algunos aprobados y otros rechazados se aprueba Parcialmente
	else update general.solicitudprestamo set estado='P'  where idsolicitud=idsoli;

	end if;
end if;


   RETURN NEW; 
END; 
$$
LANGUAGE 'plpgsql';

select * from general.solicitudequipo;
select * from general.solicitudPRESTAMO;
UPDATE general.solicitudequipo SET ESTADO='R' WHERE IDSOLICITUDEQUIPO=27;
UPDATE general.solicitudequipo SET ESTADO='R' WHERE IDSOLICITUDEQUIPO=28;
UPDATE general.solicitudPRESTAMO SET ESTADO='S' WHERE IDSOLICITUD=70;
--CREATE TRIGGER verificarEstadoSolicitud BEFORE UPDATE
--ON general.solicitudequipo
--FOR EACH ROW EXECUTE PROCEDURE general.verificarEstadoSolicitud();


SET search_path = General;
CREATE or replace FUNCTION modificarSolicitudInfraestructura() RETURNS trigger 
AS $$
declare
BEGIN 
	if new.estado='A' then
		update general.solicitudprestamo set estado='A'  where idsolicitud=new.idsolicitud;
	else if new.estado='R' then
		update general.solicitudprestamo set estado='R'  where idsolicitud=new.idsolicitud;
	end if;
		end if;

   RETURN NEW; 
END; 
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER verificarEstadoSolicitud BEFORE UPDATE
ON general.solicitudsecciones
FOR EACH ROW EXECUTE PROCEDURE general.modificarSolicitudInfraestructura();







/******************************************** inserciones ************************************************/

--personas usuarios nacionalidades auditoria previamente insertadas
select * from auditoria.auditoria;
select * from general.personas;
select * from general.usuarios;
select * from general.nacionalidades;


--telefonos
--error en la insercion de telefonos
INSERT INTO general.telefonos VALUES (1,'8895-3002');
INSERT INTO general.telefonos VALUES (1,'2460-3273') ;
UPDATE auditoria.auditoria SET idusuario = 1 WHERE (idusuario is null);
select * from general.telefonos;

--familiaActivo
select activos.ins_familiaActivo('Computadoras',1);
select activos.ins_familiaActivo('Escritorios',1);
select activos.ins_familiaActivo('Sillas',1);
select activos.ins_familiaActivo('Audiovisual',1);
select * from activos.familiaActivo;

--grupoCaracteristicas
select activos.ins_grupoCaracteristicas('Computadoras de Escritorio','Establece las características estandar para equipos de cómputo de escritorio',1);
select activos.ins_grupoCaracteristicas('Computadoras de Portátiles','Establece las características estandar para equipos de cómputo portátiles',1);
select activos.ins_grupoCaracteristicas('Proyectores de Multimedia','Establece las características estandar para equipos proyectores de multimedia',1);
select * from activos.grupoCaracteristicas;

--tipoActivo
--error cambiar tamaÃ±o para tipo de 20 a 50
select activos.ins_tipoActivo('Computadora de escritorio',1,1,1);
select activos.ins_tipoActivo('Computadora portátil',1,2,1);
select activos.ins_tipoActivo('Proyector de multimedia',1,3,1);
select activos.ins_tipoActivo('Computadora portátil (Netbook)',1,2,1);
select * from activos.tipoActivo;

--ubicacionActivo
select activos.ins_ubicacionActivo('Oficinas de Computación',1);
select activos.ins_ubicacionActivo('Laboratorio Institucional',1);
select activos.ins_ubicacionActivo('Laboratorio de Computación',1);
select * from activos.ubicacionActivo;

--detalleUbicacion
select activos.ins_detalleUbicacion(1,'Oficina Leonardo Víquez',1);
select activos.ins_detalleUbicacion(1,'Oficina Oscar Víquez',1);
select activos.ins_detalleUbicacion(1,'Oficina Lorena Valerio',1);
select activos.ins_detalleUbicacion(1,'Oficina Vera Gamboa',1);
select activos.ins_detalleUbicacion(1,'Oficina Secretaría',1);
select activos.ins_detalleUbicacion(2,'Sala1',1);
select activos.ins_detalleUbicacion(2,'Sala2',1);
select activos.ins_detalleUbicacion(3,'Sala1',1);
select activos.ins_detalleUbicacion(3,'Sala2',1);
select activos.ins_detalleUbicacion(3,'Miniauditorio',1);
select * from activos.detalleUbicacion;

--estadoActivo
select activos.ins_estadoActivo('Activo','El activo esta su estado Normal',1);
select activos.ins_estadoActivo('Dañado','El activo presenta algun desperfecto fuero de lo normal',1);
select activos.ins_estadoActivo('Extraviado','El activo no pararece',1);
select activos.ins_estadoActivo('En reparación','El activo ha sido llevado a reparar',1);

--activos
--error numero de placa acepta nulos (cuando los activos son de control)
select activos.ins_activos('000001','01-01-2010',null,'',null,null,1,1,1,1,1);
select activos.ins_activos('000002','01-01-2010',null,'',null,null,1,2,1,1,1);
select activos.ins_activos('000003','01-01-2010',null,'',null,null,1,3,1,1,1);
select * from activos.activos;


